/**
 * 
 */
package com.point.business.database.managers;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Locale;

import org.apache.log4j.Logger;

import com.point.business.database.model.History;
import com.point.business.database.model.InformationND;
import com.point.business.database.model.InformationNO;
import com.point.business.database.model.InformationSL;
import com.point.business.database.model.InformationUOPB;
import com.point.business.database.model.User;
import com.point.business.database.util.DatabaseConnection;
import com.point.business.database.util.UsersMaxLimitReachedException;
import com.point.business.util.Settings;
import com.point.business.util.UserNotFoundException;

/**
 * @author Ilija Trajkoski
 * 
 */
public class InformationManager {
	private static String MAX_ROWS = "20";
	private static InformationManager instance = new InformationManager();
	private static Logger logger = Logger.getLogger(InformationManager.class);

	private InformationManager() {
		MAX_ROWS = Settings.getValue("max.rows.per.search");
	}

	public static InformationManager getInstance() {
		return instance;
	}

	public boolean createNORecords(String evidentionNumber,
			String legalSubject, String odu, String content,
			String zalozenDoveritel, String embsZalozenDoveritel,
			String zalozenDolznik, String embsZalozenDolznik, String dolznik,
			String embsDolznik, String notar, String edib1, String edib2,
			String edib3) {
		boolean result = false;
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		try {
			statement = conn
					.prepareStatement("insert into information_no"
							+ "(evidentionNumber, legalSubject, odu, content, zalozenDoveritel, embsZalozenDoveritel, zalozenDolznik, embsZalozenDolznik, dolznik, embsDolznik, notar, edib1, edib2, edib3) "
							+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			statement.setString(1, evidentionNumber);
			statement.setString(2, legalSubject);
			statement.setString(3, odu);
			statement.setString(4, content);
			statement.setString(5, zalozenDoveritel);
			statement.setString(6, embsZalozenDoveritel);
			statement.setString(7, zalozenDolznik);
			statement.setString(8, embsZalozenDolznik);
			statement.setString(9, dolznik);
			statement.setString(10, embsDolznik);
			statement.setString(11, notar);
			statement.setString(12, edib1);
			statement.setString(13, edib2);
			statement.setString(14, edib3);
			int created = statement.executeUpdate();
			result = created == 1;
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return result;
	}

	public boolean createNDRecords(String documentNumber, String type,
			String fullName, String address, String city, String authority,
			String evidationNumber, String documentName,
			String announcmentIssueNumber, String announcmentIssueDate,
			Date anouncmentDatum) {
		boolean result = false;
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		try {
			statement = conn
					.prepareStatement("insert into information_nd"
							+ "(documentNumber, type, fullName, address, city, authority, evidationNumber, documentName, anouncmentIssueNumber, anouncmentIssueDate, anounceDatum) "
							+ "values(?,?,?,?,?,?,?,?,?,?,?)");
			statement.setString(1, documentNumber);
			statement.setString(2, type);
			statement.setString(3, fullName);
			statement.setString(4, address);
			statement.setString(5, city);
			statement.setString(6, authority);
			statement.setString(7, evidationNumber);
			statement.setString(8, documentName);
			statement.setString(9, announcmentIssueNumber);
			statement.setString(10, announcmentIssueDate);
			statement.setDate(11, new java.sql.Date(anouncmentDatum.getTime()));
			int created = statement.executeUpdate();
			result = created == 1;
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return result;
	}

	public boolean createSLRecords(String procedure, String title, String embs,
			String anounceDate, String uuid, String desctription,
			String anounceBy, String slv, Date anouncmentDatum) {
		boolean result = false;
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		try {
			statement = conn
					.prepareStatement("insert into information_sl"
							+ "(`procedure`, title, embs, anounceDate, `uuid`, description, anounceBy, slv, anounceDatum) "
							+ "values(?,?,?,?,?,?,?,?,?)");
			statement.setString(1, procedure);
			statement.setString(2, title);
			statement.setString(3, embs);
			statement.setString(4, anounceDate);
			statement.setString(5, uuid);
			statement.setString(6, desctription);
			statement.setString(7, anounceBy);
			statement.setString(8, slv);
			statement.setDate(9, new java.sql.Date(anouncmentDatum.getTime()));

			int created = statement.executeUpdate();
			result = created == 1;
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return result;
	}

	/**
	 * Searches SL information by EMBS You can search by all values if they are
	 * not null, otherwise it will exclude the value that is null Regardless it
	 * will always return not more than 20 results
	 * 
	 * @param User
	 *            we need this for making history, rest of the params are null
	 *            acceptable
	 * @return list of information and stores into history
	 * @throws UsersMaxLimitReachedException
	 */
	public List<InformationSL> searchSL(int userId, String embs, String uuid,
			String procedure, String title, String description)
			throws UsersMaxLimitReachedException {
		try {
			UsersManager.getInstance().testConnection();
		} catch (UserNotFoundException ex) {
			logger.error(ex);
		}
		User user = UsersManager.getInstance().getUserById(userId);
		if (user.getQueriesCount() >= user.getMaxSearchQueries()) {
			throw new UsersMaxLimitReachedException();
		}
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		List<InformationSL> result = new ArrayList<InformationSL>();
		String whereClause = makeSLWhereClause(embs, uuid, procedure, title,
				description);
		try {

			statement = conn
					.prepareStatement("SELECT * FROM information_sl WHERE 1=1 "
							+ whereClause + " LIMIT 0,40");
			int position = 1;
			if (embs != null) {
				statement.setString(position, "%" + embs + "%");
				position++;
				whereClause = whereClause.replaceFirst("\\?", "'%" + embs
						+ "%'");
			}
			if (uuid != null) {
				statement.setString(position, "%" + uuid + "%");
				position++;
				whereClause = whereClause.replaceFirst("\\?", "'%" + uuid
						+ "%'");
			}
			if (procedure != null) {
				statement.setString(position, "%" + procedure + "%");
				position++;
				whereClause = whereClause.replaceFirst("\\?", "'%" + procedure
						+ "%'");
			}
			if (description != null) {
				statement.setString(position, "%" + description + "%");
				position++;
				whereClause = whereClause.replaceFirst("\\?", "'%"
						+ description + "%'");
			}
			if (description != null) {
				statement.setString(position, "%" + description + "%");
				position++;
				whereClause = whereClause.replaceFirst("\\?", "'%"
						+ description + "%'");
			}
			ResultSet resultSet = statement.executeQuery();
			convertResultSetToListSL(result, resultSet);
			resultSet.close();
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		String theWholeQuery = "SELECT * FROM information_sl WHERE 1=1 "
				+ whereClause + " LIMIT 0," + MAX_ROWS;
		addHistory(userId, theWholeQuery, "SL", result.size());
		UsersManager.getInstance().incrementUserQueriesCount(userId);
		return result;
	}

	public List<InformationNO> searchNO(int userId, String embs, String any)
			throws UsersMaxLimitReachedException {
		try {
			UsersManager.getInstance().testConnection();
		} catch (UserNotFoundException ex) {
			logger.error(ex);
		}
		User user = UsersManager.getInstance().getUserById(userId);
		if (user.getQueriesCount() >= user.getMaxSearchQueries()) {
			throw new UsersMaxLimitReachedException();
		}
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		List<InformationNO> result = new ArrayList<InformationNO>();
		String whereClause = makeNOWhereClause(any);
		try {
			statement = conn
					.prepareStatement("SELECT * FROM information_no WHERE "
							+ whereClause + " LIMIT 0," + MAX_ROWS);
			ResultSet resultSet = statement.executeQuery();
			convertResultSetToListNO(result, resultSet);
			resultSet.close();
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		String theWholeQuery = "SELECT * FROM information_no WHERE "
				+ whereClause + " LIMIT 0," + MAX_ROWS;
		addHistory(userId, theWholeQuery, "NO", result.size());
		logger.error(theWholeQuery);
		UsersManager.getInstance().incrementUserQueriesCount(userId);
		return result;
	}

	public List<InformationND> searchND(int userId, String type,
			String documentNumber, String fullName)
			throws UsersMaxLimitReachedException {
		try {
			UsersManager.getInstance().testConnection();
		} catch (UserNotFoundException ex) {
			logger.error(ex);
		}
		User user = UsersManager.getInstance().getUserById(userId);
		if (user.getQueriesCount() >= user.getMaxSearchQueries()) {
			throw new UsersMaxLimitReachedException();
		}
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		List<InformationND> result = new ArrayList<InformationND>();
		String whereClause = makeNDWhereClause(type, documentNumber, fullName);
		try {
			statement = conn
					.prepareStatement("SELECT * FROM information_nd WHERE 1=1 "
							+ whereClause + " LIMIT 0," + MAX_ROWS);
			int position = 1;
			if (type != null) {
				statement.setString(position, "%" + type + "%");
				position++;
				whereClause = whereClause.replaceFirst("\\?", "'%" + type
						+ "%'");
			}
			if (documentNumber != null) {
				statement.setString(position, "%" + documentNumber + "%");
				position++;
				whereClause = whereClause.replaceFirst("\\?", "'%"
						+ documentNumber + "%'");
			}
			if (fullName != null) {
				statement.setString(position, "%" + fullName + "%");
				position++;
				whereClause = whereClause.replaceFirst("\\?", "'%" + fullName
						+ "%'");
			}
			ResultSet resultSet = statement.executeQuery();
			convertResultSetToListND(result, resultSet);
			resultSet.close();
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		String theWholeQuery = "SELECT * FROM information_nd WHERE 1=1 "
				+ whereClause + " LIMIT 0," + MAX_ROWS;
		addHistory(userId, theWholeQuery, "ND", result.size());
		UsersManager.getInstance().incrementUserQueriesCount(userId);
		return result;
	}

	public String makeNDWhereClause(String type, String documentNumber,
			String fullName) {
		String whereClause = type != null ? " and type LIKE ? " : "";
		whereClause += documentNumber != null ? " and documentNumber LIKE ? "
				: "";
		whereClause += fullName != null ? " and fullName LIKE ? " : "";
		whereClause += " ORDER BY anounceDatum DESC";
		return whereClause;
	}

	private String makeNOWhereClause(String any) {
		String whereClause = "";

		if (any != null) {
			whereClause += " embsZalozenDoveritel LIKE '%" + any + "%' OR";
			whereClause += " embsZalozenDolznik LIKE '%" + any + "%' OR";
			whereClause += " embsDolznik LIKE '%" + any + "%' OR";
			whereClause += " embsZalozenDoveritel LIKE '%" + any + "%' OR";
			whereClause += " evidentionNumber LIKE '%" + any + "%' OR";
			whereClause += " legalSubject LIKE '%" + any + "%' OR";
			whereClause += " odu LIKE '%" + any + "%' OR";
			whereClause += " content LIKE '%" + any + "%' OR";
			whereClause += " zalozenDoveritel LIKE '%" + any + "%' OR";
			whereClause += " zalozenDolznik LIKE '%" + any + "%' OR";
			whereClause += " dolznik LIKE '%" + any + "%' OR";
			whereClause += " notar LIKE '%" + any + "%' OR";
			whereClause += " edib1 LIKE '%" + any + "%' OR";
			whereClause += " edib2 LIKE '%" + any + "%' OR";
			whereClause += " edib3 LIKE '%" + any + "%'";
		}
		whereClause += " ORDER BY insertionDate";
		return whereClause;
	}

	/**
	 * @param embs
	 * @param procedure
	 * @param title
	 * @param description
	 * @return
	 */
	private String makeSLWhereClause(String embs, String uuid,
			String procedure, String title, String description) {
		String whereClause = embs != null ? " and embs LIKE ? " : "";
		whereClause += uuid != null ? " and uuid LIKE ? " : "";
		whereClause += procedure != null ? " and `procedure` LIKE ? " : "";
		// whereClause += title != null ? " and title LIKE ? " : "";
		whereClause += description != null ? " and (description LIKE ? or title LIKE ?)"
				: "";
		whereClause += " ORDER BY anounceDatum DESC";
		return whereClause;
	}

	/**
	 * @param embs
	 * @param procedure
	 * @param title
	 * @param description
	 * @return
	 */
	private String makeUOPBWhereClause(String embs, String uuid,
			String procedure, String title, String description) {
		String whereClause = embs != null ? " and embs LIKE ? " : "";
		whereClause += uuid != null ? " and uuid LIKE ? " : "";
		whereClause += procedure != null ? " and `procedure` LIKE ? " : "";
		whereClause += title != null ? " and title LIKE ? " : "";
		whereClause += description != null ? " and description LIKE ? " : "";
		whereClause += " ORDER BY insertionDate";
		return whereClause;
	}

	/**
	 * @param result
	 * @param resultSet
	 * @throws SQLException
	 */
	private void convertResultSetToListSL(List<InformationSL> result,
			ResultSet resultSet) throws SQLException {
		while (resultSet.next()) {
			InformationSL informationSL = new InformationSL();
			informationSL.setId(resultSet.getInt("id"));
			informationSL.setEmbs(resultSet.getString("embs"));
			informationSL.setProcedure(resultSet.getString("procedure"));
			informationSL.setDesctription(resultSet.getString("description"));
			informationSL.setTitle(resultSet.getString("title"));
			informationSL.setUuid(resultSet.getString("uuid"));
			informationSL.setAnounceBy(resultSet.getString("anounceBy"));
			informationSL.setSlv(resultSet.getString("slv"));

			DateFormat outputFormat = new SimpleDateFormat("dd/MM/yyyy");
			Date date = new java.util.Date(resultSet.getDate("anounceDatum")
					.getTime());

			if (date != null) {
				String myDate = outputFormat.format(date);
				informationSL.setAnounceDate(myDate);
			}
			if (resultSet.getTimestamp("insertionDate") != null) {
				informationSL.setInsertionDate(new Date(resultSet.getTimestamp(
						"insertionDate").getTime()));
			}
			result.add(informationSL);
		}
	}

	/**
	 * @param result
	 * @param resultSet
	 * @throws SQLException
	 */
	private void convertResultSetToListUOPB(List<InformationUOPB> result,
			ResultSet resultSet) throws SQLException {
		while (resultSet.next()) {
			InformationUOPB informationUOPB = new InformationUOPB();
			informationUOPB.setId(resultSet.getInt("id"));
			informationUOPB.setEmbs(resultSet.getString("embs"));
			informationUOPB.setProcedure(resultSet.getString("procedure"));
			informationUOPB.setDesctription(resultSet.getString("description"));
			informationUOPB.setTitle(resultSet.getString("title"));
			informationUOPB.setUuid(resultSet.getString("uuid"));
			informationUOPB.setAnounceBy(resultSet.getString("anounceBy"));
			informationUOPB.setSlv(resultSet.getString("slv"));

			DateFormat outputFormat = new SimpleDateFormat("dd/MM/yyyy");
			DateFormat formatter = new SimpleDateFormat(
					"EEE MMM dd HH:mm:ss Z yyyy", Locale.US);
			Date date = null;
			try {
				date = (Date) formatter.parse(resultSet
						.getString("anounceDate"));
			} catch (ParseException e) {
				logger.error(e);
			}
			if (date != null) {
				String myDate = outputFormat.format(date);
				informationUOPB.setAnounceDate(myDate);
			}
			if (resultSet.getTimestamp("insertionDate") != null) {
				informationUOPB.setInsertionDate(new Date(resultSet
						.getTimestamp("insertionDate").getTime()));
			}
			result.add(informationUOPB);
		}
	}

	private void convertResultSetToListNO(List<InformationNO> result,
			ResultSet resultSet) throws SQLException {
		while (resultSet.next()) {
			InformationNO informationNO = new InformationNO();
			informationNO.setId(resultSet.getInt("id"));
			informationNO.setEvidentionNumber(resultSet
					.getString("evidentionNumber"));
			informationNO.setLegalSubject(resultSet.getString("legalSubject"));
			informationNO.setOdu(resultSet.getString("odu"));
			informationNO.setContent(resultSet.getString("content"));
			informationNO.setZalozenDoveritel(resultSet
					.getString("zalozenDoveritel"));
			informationNO.setEmbsZalozenDoveritel(resultSet
					.getString("embsZalozenDoveritel"));
			informationNO.setZalozenDolznik(resultSet
					.getString("zalozenDolznik"));
			informationNO.setEmbsZalozenDolznik(resultSet
					.getString("embsZalozenDolznik"));
			informationNO.setDolznik(resultSet.getString("dolznik"));
			informationNO.setNotar(resultSet.getString("notar"));
			informationNO.setEdib1(resultSet.getString("edib1"));

			DateFormat outputFormat = new SimpleDateFormat("dd/MM/yyyy");
			DateFormat formatter = new SimpleDateFormat(
					"EEE MMM dd HH:mm:ss Z yyyy", Locale.US);
			Date date = null;
			try {
				date = (Date) formatter.parse(resultSet.getString("edib2"));
			} catch (ParseException e) {
				logger.error(e);
			}
			if (date != null) {
				String myDate = outputFormat.format(date);
				informationNO.setEdib2(myDate);
			}

			informationNO.setEdib3(resultSet.getString("edib3"));
			if (resultSet.getTimestamp("insertionDate") != null) {
				informationNO.setInsertionDate(new Date(resultSet.getTimestamp(
						"insertionDate").getTime()));
			}
			result.add(informationNO);
		}
	}

	private void convertResultSetToListND(List<InformationND> result,
			ResultSet resultSet) throws SQLException {
		while (resultSet.next()) {
			InformationND informationND = new InformationND();
			informationND.setId(resultSet.getInt("id"));
			informationND.setDocumentNumber(resultSet
					.getString("documentNumber"));
			informationND.setType(resultSet.getString("type"));
			informationND.setFullName(resultSet.getString("fullName"));
			informationND.setAddress(resultSet.getString("address"));
			informationND.setCity(resultSet.getString("city"));
			informationND.setAuthority(resultSet.getString("authority"));
			informationND.setEvidationNumber(resultSet
					.getString("evidationNumber"));
			informationND.setAnnouncmentIssueNumber(resultSet
					.getString("anouncmentIssueNumber"));
			informationND.setDocumentName(resultSet.getString("documentName"));
			DateFormat outputFormat = new SimpleDateFormat("dd/MM/yyyy");
			Date date = new java.util.Date(resultSet.getDate("anounceDatum")
					.getTime());

			if (date != null) {
				String myDate = outputFormat.format(date);
				informationND.setAnnouncmentIssueDate(myDate);
			}
			if (resultSet.getTimestamp("insertionDate") != null) {
				informationND.setInsertionDate(new Date(resultSet.getTimestamp(
						"insertionDate").getTime()));
			}
			result.add(informationND);
		}
	}

	public void addHistory(int userId, String query, String searchedObject,
			int results) {
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		try {
			statement = conn
					.prepareStatement("insert into history"
							+ "(userId, searchQuery, searchObject, searchDate, gotResults) "
							+ "values(?,?,?,?,?)");
			statement.setInt(1, userId);
			statement.setString(2, query);
			statement.setString(3, searchedObject);
			statement.setTimestamp(4, new Timestamp(new Date().getTime()));
			statement.setInt(5, results);
			statement.executeUpdate();
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
	}

	public List<String> getNDTypes() {
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		List<String> result = new ArrayList<String>();
		try {
			statement = conn
					.prepareStatement("SELECT DISTINCT `type` FROM information_nd");
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				result.add(resultSet.getString(1));
			}
			resultSet.close();
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return result;
	}

	public List<String> getSLTypes() {
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		List<String> result = new ArrayList<String>();
		try {
			statement = conn
					.prepareStatement("SELECT DISTINCT `procedure` FROM information_sl");
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				result.add(resultSet.getString(1));
			}
			resultSet.close();
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return result;
	}

	public InformationSL getInformationSLById(int id) {
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		InformationSL object = null;
		List<InformationSL> result = new ArrayList<InformationSL>();
		try {

			statement = conn
					.prepareStatement("SELECT * FROM information_sl WHERE id=?");
			statement.setInt(1, id);
			ResultSet resultSet = statement.executeQuery();
			convertResultSetToListSL(result, resultSet);
			if (result.size() > 0) {
				object = result.get(0);
			}
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return object;
	}

	public History getLastHistory(int userId) {
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		History result = null;
		try {
			statement = conn
					.prepareStatement("select * from history where userId=? and searchDate = (select max(searchDate) from history where userId=?)");
			statement.setInt(1, userId);
			statement.setInt(2, userId);
			ResultSet resultSet = statement.executeQuery();
			if (resultSet.next()) {
				History history = new History();
				history.setId(resultSet.getInt("id"));
				history.setSearchQuery(resultSet.getString("searchQuery"));
				history.setUserId(resultSet.getInt("userId"));
				history.setSearchObject(resultSet.getString("searchObject"));
				if (resultSet.getTimestamp("searchDate") != null) {
					history.setSearchDate(new Date(resultSet.getTimestamp(
							"searchDate").getTime()));
				}
				result = history;
			}
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return result;
	}

	public List<InformationSL> searchSL(String searchQuery,
			Date lastNotificationDate) {
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		List<InformationSL> result = new ArrayList<InformationSL>();
		try {
			statement = conn.prepareStatement(searchQuery);
			ResultSet resultSet = statement.executeQuery();
			convertResultSetToListSL(result, resultSet);
			resultSet.close();
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return filterListSl(result, lastNotificationDate);
	}

	public List<InformationND> searchND(String searchQuery,
			Date lastNotificationDate) {
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		List<InformationND> result = new ArrayList<InformationND>();
		try {
			statement = conn.prepareStatement(searchQuery);
			ResultSet resultSet = statement.executeQuery();
			convertResultSetToListND(result, resultSet);
			resultSet.close();
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return filterListND(result, lastNotificationDate);
	}

	public List<InformationNO> searchNO(String searchQuery,
			Date lastNotificationDate) {
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		List<InformationNO> result = new ArrayList<InformationNO>();
		try {
			statement = conn.prepareStatement(searchQuery);
			ResultSet resultSet = statement.executeQuery();
			convertResultSetToListNO(result, resultSet);
			resultSet.close();
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return filterListNO(result, lastNotificationDate);
	}

	/**
	 * Searches UOPB information by EMBS You can search by all values if they
	 * are not null, otherwise it will exclude the value that is null Regardless
	 * it will always return not more than 20 results
	 * 
	 * @param User
	 *            we need this for making history, rest of the params are null
	 *            acceptable
	 * @return list of information and stores into history
	 * @throws UsersMaxLimitReachedException
	 */
	public List<InformationUOPB> searchUOPB(int userId, String embs,
			String uuid, String procedure, String title, String description)
			throws UsersMaxLimitReachedException {
		try {
			UsersManager.getInstance().testConnection();
		} catch (UserNotFoundException ex) {
			logger.error(ex);
		}
		User user = UsersManager.getInstance().getUserById(userId);
		if (user.getQueriesCount() >= user.getMaxSearchQueries()) {
			throw new UsersMaxLimitReachedException();
		}
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		List<InformationUOPB> result = new ArrayList<InformationUOPB>();
		String whereClause = makeUOPBWhereClause(embs, uuid, procedure, title,
				description);
		try {

			statement = conn
					.prepareStatement("SELECT * FROM information_uopb WHERE 1=1 "
							+ whereClause + " LIMIT 0,20");
			int position = 1;
			if (embs != null) {
				statement.setString(position, "%" + embs + "%");
				position++;
				whereClause = whereClause.replaceFirst("\\?", "'%" + embs
						+ "%'");
			}
			if (uuid != null) {
				statement.setString(position, "%" + uuid + "%");
				position++;
				whereClause = whereClause.replaceFirst("\\?", "'%" + uuid
						+ "%'");
			}
			if (procedure != null) {
				statement.setString(position, "%" + procedure + "%");
				position++;
				whereClause = whereClause.replaceFirst("\\?", "'%" + procedure
						+ "%'");
			}
			if (title != null) {
				statement.setString(position, "%" + title + "%");
				position++;
				whereClause = whereClause.replaceFirst("\\?", "'%" + title
						+ "%'");
			}
			if (description != null) {
				statement.setString(position, "%" + description + "%");
				position++;
				whereClause = whereClause.replaceFirst("\\?", "'%"
						+ description + "%'");
			}
			ResultSet resultSet = statement.executeQuery();
			convertResultSetToListUOPB(result, resultSet);
			resultSet.close();
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		String theWholeQuery = "SELECT * FROM information_uopb WHERE 1=1 "
				+ whereClause + " LIMIT 0," + MAX_ROWS;
		addHistory(userId, theWholeQuery, "UOPB", result.size());
		UsersManager.getInstance().incrementUserQueriesCount(userId);
		return result;
	}

	public List<InformationUOPB> searchUOPB(String searchQuery,
			Date lastNotificationDate) {
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		List<InformationUOPB> result = new ArrayList<InformationUOPB>();
		try {
			statement = conn.prepareStatement(searchQuery);
			ResultSet resultSet = statement.executeQuery();
			convertResultSetToListUOPB(result, resultSet);
			resultSet.close();
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return result;
	}

	private List<InformationSL> filterListSl(List<InformationSL> list,
			Date lastNotificationDate) {
		List<InformationSL> result = new ArrayList<InformationSL>();
		if (lastNotificationDate == null) {
			// dont filter
			return list;
		}
		for (InformationSL element : list) {
			if (lastNotificationDate.getTime() < element.getInsertionDate()
					.getTime()) {
				result.add(element);
			}
		}
		return result;
	}

	private List<InformationND> filterListND(List<InformationND> list,
			Date lastNotificationDate) {
		List<InformationND> result = new ArrayList<InformationND>();
		if (lastNotificationDate == null) {
			// dont filter
			return list;
		}
		for (InformationND element : list) {
			if (lastNotificationDate.getTime() < element.getInsertionDate()
					.getTime()) {
				result.add(element);
			}
		}
		return result;
	}

	private List<InformationNO> filterListNO(List<InformationNO> list,
			Date lastNotificationDate) {
		List<InformationNO> result = new ArrayList<InformationNO>();
		if (lastNotificationDate == null) {
			// dont filter
			return list;
		}
		for (InformationNO element : list) {
			if (lastNotificationDate.getTime() < element.getInsertionDate()
					.getTime()) {
				result.add(element);
			}
		}
		return result;
	}

	public Collection<? extends InformationSL> searchSLArchive(int userId,
			String embs, String uuid, String procedure, String title,
			String description) {
		try {
			UsersManager.getInstance().testConnection();
		} catch (UserNotFoundException ex) {
			logger.error(ex);
		}
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		List<InformationSL> result = new ArrayList<InformationSL>();
		String whereClause = makeSLWhereClause(embs, uuid, procedure, title,
				description);
		try {

			statement = conn
					.prepareStatement("SELECT * FROM information_sl_archive WHERE 1=1 "
							+ whereClause + " LIMIT 0,40");
			int position = 1;
			if (embs != null) {
				statement.setString(position, "%" + embs + "%");
				position++;
				whereClause = whereClause.replaceFirst("\\?", "'%" + embs
						+ "%'");
			}
			if (uuid != null) {
				statement.setString(position, "%" + uuid + "%");
				position++;
				whereClause = whereClause.replaceFirst("\\?", "'%" + uuid
						+ "%'");
			}
			if (procedure != null) {
				statement.setString(position, "%" + procedure + "%");
				position++;
				whereClause = whereClause.replaceFirst("\\?", "'%" + procedure
						+ "%'");
			}
			if (description != null) {
				statement.setString(position, "%" + description + "%");
				position++;
				whereClause = whereClause.replaceFirst("\\?", "'%"
						+ description + "%'");
			}
			if (description != null) {
				statement.setString(position, "%" + description + "%");
				position++;
				whereClause = whereClause.replaceFirst("\\?", "'%"
						+ description + "%'");
			}
			ResultSet resultSet = statement.executeQuery();
			convertResultSetToListSL(result, resultSet);
			resultSet.close();
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		String theWholeQuery = "SELECT * FROM information_sl_archive WHERE 1=1 "
				+ whereClause + " LIMIT 0," + MAX_ROWS;
		addHistory(userId, theWholeQuery, "SL", result.size());
		UsersManager.getInstance().incrementUserQueriesCount(userId);
		return result;
	}
}
